﻿

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace JiaBao.Services.CustomerService
{
    public class CustomerService : ICustomerService
    {
        public int ExecuteNonQuery(string sql, params SqlParameter[] parameter)
        {
            try
            {
                return SqlHelper.ExecuteNonQuery(sql, parameter);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public DataTable GetAllCustomers()
        {
            try
            {
                return SqlHelper.ExecuteDataTable("select * from Customer_Z order by id asc");
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public DataTable GetCustomersByAucId(int aucid)
        {
            try
            {
                if (-100 == aucid)
                    return GetAllCustomers();
                else
                    return SqlHelper.ExecuteDataTable("SELECT * from Customer_Z where " +
                        "客户标识 in (select distinct 客户标识Z from jtdjall where 拍卖会id = @aucid) " +
                        "or 客户标识 in (select distinct 客户标识Z from salerall where 拍卖会id = @aucid) order by id asc",
                        new SqlParameter("aucid", aucid));
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        public DataTable GetCustomersByAucIdAndBuyerOrSaler(int aucid, bool? buyer)
        {
            try
            {
                if (-100 == aucid)
                    switch (buyer)
                    {
                        case null:
                            return GetAllCustomers();

                        case true:
                            return SqlHelper.ExecuteDataTable("select * from Customer_Z where 客户类型 = '买家' order by id asc");

                        case false:
                            return SqlHelper.ExecuteDataTable("select * from Customer_Z where 客户类型 = '卖家' order by id asc");
                    }

                else
                    switch (buyer)
                    {
                        case null:
                            return GetCustomersByAucId(aucid);


                        case true:
                            return SqlHelper.ExecuteDataTable("SELECT * from Customer_Z where " +
                                    "(客户标识 in (select distinct 客户标识Z from jtdjall where 拍卖会id = @aucid) " +
                                    "or 客户标识 in (select distinct 客户标识Z from salerall where 拍卖会id = @aucid)) and 客户类型 = '买家'  order by id asc",
                                    new SqlParameter("aucid", aucid));
                        case false:
                            return SqlHelper.ExecuteDataTable("SELECT * from Customer_Z where " +
                                    "(客户标识 in (select distinct 客户标识Z from jtdjall where 拍卖会id = @aucid) " +
                                    "or 客户标识 in (select distinct 客户标识Z from salerall where 拍卖会id = @aucid)) and 客户类型 = '卖家'  order by id asc",
                                    new SqlParameter("aucid", aucid));
                    }
                throw  new Exception ("查询错误");
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        public DataTable GetByBirthday(DateTime start, DateTime end)
        {
            try
            {
                var sstart = start.ToString("MM-dd");
                var send = end.ToString("MM-dd");
                if (string.IsNullOrEmpty(sstart) || string.IsNullOrEmpty(send))
                    throw new Exception("日期错误");

                return SqlHelper.ExecuteDataTable("select *from customer_z  WHERE  SUBSTRING ( 出生日期,6,10) between @start and @end order by id asc",
                    new SqlParameter("start", sstart), new SqlParameter("end", send));

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public bool Login(string uid, string pwd)
        {
            try
            {
                return SqlHelper.ExecuteScalar("select 口令 from passuser where 用户名 =@uid", new SqlParameter("uid", uid)).ToString() == pwd;
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        public int Update()
        {
            try
            {
                return SqlHelper.StoredProcedureNonQuery("Z_Customer_Update");
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public bool UpdateKHBS(string oldbs, string newbs)
        {
            try
            {
                var i = SqlHelper.ExecuteNonQuery("update jtdjall set 客户标识Z = @newbs where 客户标识Z = @oldbs", new SqlParameter("newbs", newbs), new SqlParameter("oldbs", oldbs));
                var i2 = SqlHelper.ExecuteNonQuery("update salerall set 客户标识Z = @newbs where 客户标识Z = @oldbs", new SqlParameter("newbs", newbs), new SqlParameter("oldbs", oldbs));
                var i3 = SqlHelper.ExecuteNonQuery("delete from  Customer_Z where 客户标识 = @oldbs", new SqlParameter("oldbs", oldbs));

                return true;
            }
            catch (Exception)
            {

                return false;
            }
        }

        public int UpdateSum(string ids)
        {
            try
            {
                return SqlHelper.StoredProcedureNonQuery("Z_Customer_Update_Sum", new SqlParameter("@ids", ids));
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        IList<string> ICustomerService.GetFields()
        {
            try
            {
                List<string> l = new List<string>();
                var dt = SqlHelper.ExecuteDataTable(" select b.name as 表名,a.name as 列名 from sys.columns as a join sys.tables as b on a.object_id = b.object_id where b.name = 'Customer_Z' and a.name not in ('ID', '客户标识', '客户来源') ");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    l.Add(dt.Rows[i][1].ToString());
                }
                return l;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }


    }
}
